<?php
class ModelReportInventory extends Model 
{
	public function getListByBrand($brandid, $fromdate, $todate, $where = "" )
	{
		$sql = "Select b.brandid, b.brandname, g.groupid, g.groupname, p.productid, p.productname, p.barcode, p.unit, p.netprice as importcost, ord.sumimportqty, ord.sumexportqty from `product_group` as g, `product_brand` as b, `product` as p left join (select productid, sum(importqty) as sumimportqty, (sum(exportqty)*-1) as sumexportqty from `order`,`order_detail` where order.orderid = order_detail.orderid and order.orderdate >= '".$fromdate."' and order.orderdate <= '".$todate."' group by productid) as ord on ord.productid = p.productid  where p.groupid = g.groupid and g.brandid = b.brandid and b.brandid like '".$brandid."'";
		//return $sql;
		$query = $this->db->query($sql);
		return $query->rows;
	}
	
	
	public function getListByGroup($brandid, $groupid, $fromdate, $todate, $where = "" )
	{
		$sql = "Select b.brandid, b.brandname, g.groupid, g.groupname, p.productid, p.productname, p.barcode, p.unit, p.netprice as importcost, ord.sumimportqty, ord.sumexportqty from `product_group` as g, `product_brand` as b, `product` as p left join (select productid, sum(importqty) as sumimportqty, (sum(exportqty)*-1) as sumexportqty from `order`,`order_detail` where order.orderid = order_detail.orderid and order.orderdate >= '".$fromdate."' and order.orderdate <= '".$todate."' group by productid) as ord on ord.productid = p.productid  where p.groupid = g.groupid and g.brandid = b.brandid and b.brandid like '".$brandid."' and g.groupid like '".$groupid."'";
		//return $sql;
		$query = $this->db->query($sql);
		return $query->rows;
	}
	
	public function getListByStock($stockid, $fromdate, $todate, $where = "" )
	{
		$sql = "Select b.brandid, b.brandname, g.groupid, g.groupname, ord.stockid, ord.stockname, p.productid, p.productname, p.barcode, p.unit, p.netprice as importcost, ord.sumimportqty, ord.sumexportqty from `product_group` as g, `product_brand` as b, `product` as p left join (select productid, st.stockid, st.stockname, sum(importqty) as sumimportqty, (sum(exportqty)*-1) as sumexportqty from `order`,`order_detail`, `stock` as st where order.orderid = order_detail.orderid and st.stockid = order_detail.stockid and order.orderdate >= '".$fromdate."' and order.orderdate <= '".$todate."' group by st.stockid, productid) as ord on ord.productid = p.productid  where p.groupid = g.groupid and g.brandid = b.brandid and ord.stockid like '".$stockid."' and ord.stockid = '".$stockid."'";
		//return $sql;
		$query = $this->db->query($sql);
		return $query->rows;
	}
	
	public function getListByOutStock($brandid, $groupid, $fromdate, $todate, $where = "" )
	{
		$sql = "Select b.brandid, b.brandname, g.groupid, g.groupname, p.productid, p.productname, p.barcode, p.unit, p.notifyqty, p.netprice as importcost, ord.sumimportqty, ord.sumexportqty from `product_group` as g, `product_brand` as b, `product` as p left join (select productid, sum(importqty) as sumimportqty, (sum(exportqty)*-1) as sumexportqty from `order`,`order_detail` where order.orderid = order_detail.orderid and order.orderdate >= '".$fromdate."' and order.orderdate <= '".$todate."' group by productid) as ord on ord.productid = p.productid  where p.groupid = g.groupid and g.brandid = b.brandid and b.brandid like '".$brandid."' and g.groupid like '".$groupid."'";
		//return $sql;
		$query = $this->db->query($sql);
		return $query->rows;
	}
	
	public function getSumStartPeriod($productid, $fromdate)
	{
		$sql = "Select productid, (sum(importqty) + sum(exportqty)) as sumstartperiod from `order_detail` as ordt,`order` as ord where ord.orderid = ordt.orderid and ord.orderdate < '".$fromdate."' and productid = '".$productid."' group by productid";
		$query = $this->db->query($sql);
		return (float)$query->rows[0]['sumstartperiod'];
		
	}
	
}

?>